/**
 * Created by black on 2017/4/17.
 */

let sqlite3 = require('sqlite3').verbose();
let News = require('../../model/news-model');
let db = new sqlite3.Database('./database/king-land.db');
let _pageSize = 10;

let generateSearchSql = function (filter) {
    let pageNo = filter.pageNo;
    let generatedSql = `
    SELECT * FROM NEWS WHERE
      1 = 1
      ${filter && filter.season ? 'AND SEASON = ?' : ''}
      ${filter && filter.type && (filter.type != 'all') ? 'AND TYPE = ?' : ''}
      LIMIT ${(pageNo - 1) * _pageSize}, ${_pageSize}
  `;
    return generatedSql;
};

let generatePageSql = function (filter) {
    let pageNo = filter.pageNo;
    let generatedSql = `
    SELECT count(*) as count FROM NEWS WHERE
      1 = 1
      ${filter && filter.season ? 'AND SEASON = ?' : ''}
      ${filter && filter.type && (filter.type != 'all') ? 'AND TYPE = ?' : ''}
  `;
    return generatedSql;
};

/**
 *
 * @param filter
 *
 * 0|all 全部
 * 1|companyDynamics 公司动态
 * 2|industryNews 行业新闻
 * @param callback
 */

let getNewsByFilter = function (filter, callback) {
    let news = [];``
    let sql = generateSearchSql(filter);
    let pageSql = generatePageSql(filter);
    let sqlParam = [];
    filter.season && sqlParam.push(filter.season);
    filter.type && (filter.type != 'all') && sqlParam.push(filter.type);
    db.serialize(function () {
        let pagePromise = new Promise((resolve, reject) => {
            db.each(pageSql, sqlParam, function (err, n) {
                resolve(n);
            });
        });
        let dataPromise = new Promise((resolve, reject) => {
            db.each(sql, sqlParam, function (err, n) {
                news.push(new News(n.id, n.title_cn, n.title_en, n.title_es, n.content_cn, n.content_en, n.content_es, n.img_path, n.classify, n.create_time));
            }, function () {
                resolve(news);
            });
        });

        Promise.all([pagePromise, dataPromise]).then(values => {
            callback(values[1], values[0]['count']);
        });

    });

    let getNewsById = function (id, callback) {
        let news = {};
        db.serialize(function () {
            db.each(`SELECT * FROM news where id = ?`, [id], function (err, n) {
                news = new News(n.id, n.title_cn, n.title_en, n.title_es, n.content_cn, n.content_en, n.content_es, n.img_path, n.classify, n.create_time);
            }, function () {
                callback(news);
            });
        });
    };

    let saveOrUpdate = function (newsModel, callback) {
        db.serialize(function () {
            var insertSql  = `
      INSERT INTO news
        values
        (
          $id,
          $titleCn,
          $titleEn,
          $titleEs,
          $contentCn,
          $contentEn,
          $contentEs,
          $imgPath,
          $classify,
          $createTime('now', '+8 hour')
         );
    `;
            var updateSql = `
      UPDATE news SET
        title_cn = $titleCn,
        title_en = $titleEn,
        title_es = $titleEs,
        content_cn = $contentCn,
        content_en = $contentEn,
        content_es = $contentEs,
        img_path = $imgPath,
        classify = $classify,
        create_time = datetime('now', '+8 hour'),
       where id = $id
    `;
            var prepareStmt = null;
            if(newsModel.id) {
                prepareStmt = db.prepare(updateSql);
            } else {
                prepareStmt = db.prepare(insertSql);
            }
            prepareStmt.run({
                $id: newsModel.id || null,
                $titleCn: newsModel.titleCn,
                $titleEn: newsModel.titleEn,
                $titleEs: newsModel.titleEs,
                $contentCn: newsModel.contentCn,
                $contentEn: newsModel.contentEn,
                $contentEs: newsModel.contentEs,
                $imgPath: newsModel.imgPath,
                $classify: newsModel.classify,
                $createTime: newsModel.createTime
            });
            prepareStmt.finalize();
        });

    };

    let deleteById = function (id, callback) {
        db.serialize(function () {
            db.run(`delete from news where id = ?`, [id], callback);
        });
    };

};
module.exports = {
    getNewsByFilter: getNewsByFilter,
    getNewsById: getNewsById,
    saveOrUpdate: saveOrUpdate,
    deleteById: deleteById
};
